﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Matrix.Common.DBUtility;
using Matrix.Common.Utility;
using Matrix.MaKaYunShang.Model;
using Matrix.MaKaYunShang.Model.Enum;

namespace Matrix.MaKaYunShang.DAL
{
    public partial class ShopsDAL : BaseDAL<ShopsDAL>
    {
        #region 默认方法
        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Shops model)
        {
            var strSql = new StringBuilder();
            strSql.Append("insert into Shops(");
            strSql.Append("Name,Description,AreaId,Address,BusinessStatus,CloseTimeStart,CloseTimeEnd,WechatParamQrCodeId,Created,Updated,CreatedBy,UpdatedBy,Longitude,Latitude,LegalPerson,LegalPersonMobile,PostNumber,BusinessLicenseNo,Logo,SalesVolume,IsOpenOrderGrab,Enabled,SalesTerritory,MarketAreaName,BelongDistributor,Tel,IndustryId,SeparatedIndustryId,CanSendCard,FixedQrCode,IsHeadquarters,ParentShopId,IsActive,CanFrontView,IsCoordinateTransform,IsDelete");
            strSql.Append(") values (");
            strSql.Append("@Name,@Description,@AreaId,@Address,@BusinessStatus,@CloseTimeStart,@CloseTimeEnd,@WechatParamQrCodeId,@Created,@Updated,@CreatedBy,@UpdatedBy,@Longitude,@Latitude,@LegalPerson,@LegalPersonMobile,@PostNumber,@BusinessLicenseNo,@Logo,@SalesVolume,@IsOpenOrderGrab,@Enabled,@SalesTerritory,@MarketAreaName,@BelongDistributor,@Tel,@IndustryId,@SeparatedIndustryId,@CanSendCard,@FixedQrCode,@IsHeadquarters,@ParentShopId,@IsActive,@CanFrontView,@IsCoordinateTransform,@IsDelete");
            strSql.Append(") ");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
			            new SqlParameter("@Name", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Description", SqlDbType.NVarChar,150) ,            
                        new SqlParameter("@AreaId", SqlDbType.Int,4) ,            
                        new SqlParameter("@Address", SqlDbType.NVarChar,-1) ,            
                        new SqlParameter("@BusinessStatus", SqlDbType.Int,4) ,            
                        new SqlParameter("@CloseTimeStart", SqlDbType.DateTime) ,            
                        new SqlParameter("@CloseTimeEnd", SqlDbType.DateTime) ,            
                        new SqlParameter("@WechatParamQrCodeId", SqlDbType.Int,4) ,            
                        new SqlParameter("@Created", SqlDbType.DateTime) ,            
                        new SqlParameter("@Updated", SqlDbType.DateTime) ,            
                        new SqlParameter("@CreatedBy", SqlDbType.Int,4) ,            
                        new SqlParameter("@UpdatedBy", SqlDbType.Int,4) ,            
                        new SqlParameter("@Longitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@Latitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@LegalPerson", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@LegalPersonMobile", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@PostNumber", SqlDbType.NVarChar,6) ,            
                        new SqlParameter("@BusinessLicenseNo", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Logo", SqlDbType.NVarChar,200) ,            
                        new SqlParameter("@SalesVolume", SqlDbType.Int,4) ,            
                        new SqlParameter("@IsOpenOrderGrab", SqlDbType.Bit,1) ,            
                        new SqlParameter("@Enabled", SqlDbType.Bit,1) ,            
                        new SqlParameter("@SalesTerritory", SqlDbType.NVarChar,255) ,            
                        new SqlParameter("@MarketAreaName", SqlDbType.NVarChar,255) ,            
                        new SqlParameter("@BelongDistributor", SqlDbType.NVarChar,255) ,            
                        new SqlParameter("@Tel", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@IndustryId", SqlDbType.Int,4) ,            
                        new SqlParameter("@SeparatedIndustryId", SqlDbType.Int,4) ,            
                        new SqlParameter("@CanSendCard", SqlDbType.Bit,1) ,            
                        new SqlParameter("@FixedQrCode", SqlDbType.NVarChar,512) ,            
                        new SqlParameter("@IsHeadquarters", SqlDbType.Bit,1) ,            
                        new SqlParameter("@ParentShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@IsActive", SqlDbType.Bit,1) ,            
                        new SqlParameter("@CanFrontView", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsCoordinateTransform", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.Name;
            parameters[1].Value = model.Description;
            parameters[2].Value = model.AreaId;
            parameters[3].Value = model.Address;
            parameters[4].Value = model.BusinessStatus;
            parameters[5].Value = model.CloseTimeStart;
            parameters[6].Value = model.CloseTimeEnd;
            parameters[7].Value = model.WechatParamQrCodeId;
            parameters[8].Value = model.Created;
            parameters[9].Value = model.Updated;
            parameters[10].Value = model.CreatedBy;
            parameters[11].Value = model.UpdatedBy;
            parameters[12].Value = model.Longitude;
            parameters[13].Value = model.Latitude;
            parameters[14].Value = model.LegalPerson;
            parameters[15].Value = model.LegalPersonMobile;
            parameters[16].Value = model.PostNumber;
            parameters[17].Value = model.BusinessLicenseNo;
            parameters[18].Value = model.Logo;
            parameters[19].Value = model.SalesVolume;
            parameters[20].Value = model.IsOpenOrderGrab;
            parameters[21].Value = model.Enabled;
            parameters[22].Value = model.SalesTerritory;
            parameters[23].Value = model.MarketAreaName;
            parameters[24].Value = model.BelongDistributor;
            parameters[25].Value = model.Tel;
            parameters[26].Value = model.IndustryId;
            parameters[27].Value = model.SeparatedIndustryId;
            parameters[28].Value = model.CanSendCard;
            parameters[29].Value = model.FixedQrCode;
            parameters[30].Value = model.IsHeadquarters;
            parameters[31].Value = model.ParentShopId;
            parameters[32].Value = model.IsActive;
            parameters[33].Value = model.CanFrontView;
            parameters[34].Value = model.IsCoordinateTransform;
            parameters[35].Value = model.IsDelete; var obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Shops model)
        {
            var strSql = new StringBuilder();
            strSql.Append("update Shops set ");

            strSql.Append(" Name = @Name , ");
            strSql.Append(" Description = @Description , ");
            strSql.Append(" AreaId = @AreaId , ");
            strSql.Append(" Address = @Address , ");
            strSql.Append(" BusinessStatus = @BusinessStatus , ");
            strSql.Append(" CloseTimeStart = @CloseTimeStart , ");
            strSql.Append(" CloseTimeEnd = @CloseTimeEnd , ");
            strSql.Append(" WechatParamQrCodeId = @WechatParamQrCodeId , ");
            strSql.Append(" Created = @Created , ");
            strSql.Append(" Updated = @Updated , ");
            strSql.Append(" CreatedBy = @CreatedBy , ");
            strSql.Append(" UpdatedBy = @UpdatedBy , ");
            strSql.Append(" Longitude = @Longitude , ");
            strSql.Append(" Latitude = @Latitude , ");
            strSql.Append(" LegalPerson = @LegalPerson , ");
            strSql.Append(" LegalPersonMobile = @LegalPersonMobile , ");
            strSql.Append(" PostNumber = @PostNumber , ");
            strSql.Append(" BusinessLicenseNo = @BusinessLicenseNo , ");
            strSql.Append(" Logo = @Logo , ");
            strSql.Append(" SalesVolume = @SalesVolume , ");
            strSql.Append(" IsOpenOrderGrab = @IsOpenOrderGrab , ");
            strSql.Append(" Enabled = @Enabled , ");
            strSql.Append(" SalesTerritory = @SalesTerritory , ");
            strSql.Append(" MarketAreaName = @MarketAreaName , ");
            strSql.Append(" BelongDistributor = @BelongDistributor , ");
            strSql.Append(" Tel = @Tel , ");
            strSql.Append(" IndustryId = @IndustryId , ");
            strSql.Append(" SeparatedIndustryId = @SeparatedIndustryId , ");
            strSql.Append(" CanSendCard = @CanSendCard , ");
            strSql.Append(" FixedQrCode = @FixedQrCode , ");
            strSql.Append(" IsHeadquarters = @IsHeadquarters , ");
            strSql.Append(" ParentShopId = @ParentShopId , ");
            strSql.Append(" IsActive = @IsActive , ");
            strSql.Append(" CanFrontView = @CanFrontView , ");
            strSql.Append(" IsCoordinateTransform = @IsCoordinateTransform , ");
            strSql.Append(" IsDelete = @IsDelete  ");
            strSql.Append(" where Id=@Id ");

            SqlParameter[] parameters = {
			            new SqlParameter("@Id", SqlDbType.Int,4) ,            
                        new SqlParameter("@Name", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Description", SqlDbType.NVarChar,150) ,            
                        new SqlParameter("@AreaId", SqlDbType.Int,4) ,            
                        new SqlParameter("@Address", SqlDbType.NVarChar,-1) ,            
                        new SqlParameter("@BusinessStatus", SqlDbType.Int,4) ,            
                        new SqlParameter("@CloseTimeStart", SqlDbType.DateTime) ,            
                        new SqlParameter("@CloseTimeEnd", SqlDbType.DateTime) ,            
                        new SqlParameter("@WechatParamQrCodeId", SqlDbType.Int,4) ,            
                        new SqlParameter("@Created", SqlDbType.DateTime) ,            
                        new SqlParameter("@Updated", SqlDbType.DateTime) ,            
                        new SqlParameter("@CreatedBy", SqlDbType.Int,4) ,            
                        new SqlParameter("@UpdatedBy", SqlDbType.Int,4) ,            
                        new SqlParameter("@Longitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@Latitude", SqlDbType.NVarChar,16) ,            
                        new SqlParameter("@LegalPerson", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@LegalPersonMobile", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@PostNumber", SqlDbType.NVarChar,6) ,            
                        new SqlParameter("@BusinessLicenseNo", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@Logo", SqlDbType.NVarChar,200) ,            
                        new SqlParameter("@SalesVolume", SqlDbType.Int,4) ,            
                        new SqlParameter("@IsOpenOrderGrab", SqlDbType.Bit,1) ,            
                        new SqlParameter("@Enabled", SqlDbType.Bit,1) ,            
                        new SqlParameter("@SalesTerritory", SqlDbType.NVarChar,255) ,            
                        new SqlParameter("@MarketAreaName", SqlDbType.NVarChar,255) ,            
                        new SqlParameter("@BelongDistributor", SqlDbType.NVarChar,255) ,            
                        new SqlParameter("@Tel", SqlDbType.NVarChar,50) ,            
                        new SqlParameter("@IndustryId", SqlDbType.Int,4) ,            
                        new SqlParameter("@SeparatedIndustryId", SqlDbType.Int,4) ,            
                        new SqlParameter("@CanSendCard", SqlDbType.Bit,1) ,            
                        new SqlParameter("@FixedQrCode", SqlDbType.NVarChar,512) ,            
                        new SqlParameter("@IsHeadquarters", SqlDbType.Bit,1) ,            
                        new SqlParameter("@ParentShopId", SqlDbType.Int,4) ,            
                        new SqlParameter("@IsActive", SqlDbType.Bit,1) ,            
                        new SqlParameter("@CanFrontView", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsCoordinateTransform", SqlDbType.Bit,1) ,            
                        new SqlParameter("@IsDelete", SqlDbType.Bit,1)             
              
            };

            parameters[0].Value = model.Id;
            parameters[1].Value = model.Name;
            parameters[2].Value = model.Description;
            parameters[3].Value = model.AreaId;
            parameters[4].Value = model.Address;
            parameters[5].Value = model.BusinessStatus;
            parameters[6].Value = model.CloseTimeStart;
            parameters[7].Value = model.CloseTimeEnd;
            parameters[8].Value = model.WechatParamQrCodeId;
            parameters[9].Value = model.Created;
            parameters[10].Value = model.Updated;
            parameters[11].Value = model.CreatedBy;
            parameters[12].Value = model.UpdatedBy;
            parameters[13].Value = model.Longitude;
            parameters[14].Value = model.Latitude;
            parameters[15].Value = model.LegalPerson;
            parameters[16].Value = model.LegalPersonMobile;
            parameters[17].Value = model.PostNumber;
            parameters[18].Value = model.BusinessLicenseNo;
            parameters[19].Value = model.Logo;
            parameters[20].Value = model.SalesVolume;
            parameters[21].Value = model.IsOpenOrderGrab;
            parameters[22].Value = model.Enabled;
            parameters[23].Value = model.SalesTerritory;
            parameters[24].Value = model.MarketAreaName;
            parameters[25].Value = model.BelongDistributor;
            parameters[26].Value = model.Tel;
            parameters[27].Value = model.IndustryId;
            parameters[28].Value = model.SeparatedIndustryId;
            parameters[29].Value = model.CanSendCard;
            parameters[30].Value = model.FixedQrCode;
            parameters[31].Value = model.IsHeadquarters;
            parameters[32].Value = model.ParentShopId;
            parameters[33].Value = model.IsActive;
            parameters[34].Value = model.CanFrontView;
            parameters[35].Value = model.IsCoordinateTransform;
            parameters[36].Value = model.IsDelete; return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("update Shops set IsDelete=1 ");
            strSql.Append(" where Id=@Id");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            return DbHelperSQL.ExecuteSql(strSql.ToString(), parameters) > 0;
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        public bool DeleteList(string Idlist)
        {
            var strSql = new StringBuilder();
            strSql.Append("update Shops set IsDelete=1 ");
            strSql.Append(" where Id in (" + Idlist + ")  ");
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public DataSet GetModel(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("select Id, Name, Description, AreaId, Address, BusinessStatus, CloseTimeStart, CloseTimeEnd, WechatParamQrCodeId, Created, Updated, CreatedBy, UpdatedBy, Longitude, Latitude, LegalPerson, LegalPersonMobile, PostNumber, BusinessLicenseNo, Logo, SalesVolume, IsOpenOrderGrab, Enabled, SalesTerritory, MarketAreaName, BelongDistributor, Tel, IndustryId, SeparatedIndustryId, CanSendCard, FixedQrCode, IsHeadquarters, ParentShopId, IsActive, CanFrontView, IsCoordinateTransform, IsDelete  ");
            strSql.Append("  from Shops ");
            strSql.Append(" where Id=@Id  and IsDelete=0");
            SqlParameter[] parameters = {
					new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;

            var ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * ");
            strSql.Append(" FROM Shops where IsDelete=0 ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }
        #endregion

        #region 扩展方法

        /// <summary>
        /// 获取分页数据
        /// </summary>
        public List<Shops> GetPagingList(int pageIndex, int pageSize, string searchKeyWord, string colOrder, int parentShopId, ref int totalCount)
        {
            string strWhere = " IsDelete = 0 ";
            if (parentShopId > 0)
            {
                strWhere += " and ParentShopId=" + parentShopId;
            }
            if (string.IsNullOrEmpty(colOrder))
            {
                colOrder = "Created";
            }

            if (!string.IsNullOrEmpty(searchKeyWord))
            {
                strWhere += " AND (Name LIKE '%" + searchKeyWord + "%' OR Description LIKE '%" + searchKeyWord +
                            "%' OR Address LIKE '%" + searchKeyWord + "%' OR LegalPerson LIKE '%" + searchKeyWord +
                            "%' OR LegalPersonMobile LIKE '%" + searchKeyWord + "%') ";
            }

            return GetSimpleListPage<Shops>(pageIndex, pageSize, "Shops", null, null, colOrder, strWhere,
                ref totalCount);
        }

        /// <summary>
        /// 获取满足指定半径范围内的店铺列表
        /// </summary>
        public List<Shops> GetMatchedShopList(string longitude, string latitude, float radius, string searchKeyword = "", bool onlyCanFrontView = true)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("SELECT ids.ParentId,ids.Name as IndustryName,Shops.Id, Shops.Name, Description, AreaId, Address, BusinessStatus, CloseTimeStart, CloseTimeEnd, ");
            sbSql.Append("WechatParamQrCodeId, Created, Updated, CreatedBy, UpdatedBy, Longitude, Latitude, CanSendCard,");
            sbSql.Append("LegalPerson, LegalPersonMobile, PostNumber, BusinessLicenseNo, Logo, SalesVolume, ");
            sbSql.Append("IsOpenOrderGrab, Enabled, SalesTerritory, MarketAreaName, BelongDistributor, Tel,Shops.IsDelete,  ");
            sbSql.Append("dbo.fnGetDistance(@Latitude, @Longitude, Latitude,  Longitude) AS Distance ");
            sbSql.Append("FROM Shops  left join Industry ids on ids.Id=Shops.IndustryId ");
            sbSql.Append("WHERE dbo.fnGetDistance( @Latitude, @Longitude, Latitude, Longitude)<@Radius AND Shops.IsDelete=0 AND IsActive=1 ");
            if (onlyCanFrontView)
            {
                sbSql.Append(" and CanFrontView=1");
            }
            var parameters = new List<SqlParameter>
	        {
	            new SqlParameter("@Longitude", longitude),
	            new SqlParameter("@Latitude", latitude),
	            new SqlParameter("@Radius", radius)
	        };
            if (!string.IsNullOrWhiteSpace(searchKeyword))
            {
                sbSql.Append("AND Shops.Name LIKE @Keywords ");
                parameters.Add(new SqlParameter("@Keywords", "%" + searchKeyword + "%"));
            }
            sbSql.Append("ORDER BY Distance");
            var lsShop = DbHelperSQL.ExecuteSqlList<Shops>(sbSql.ToString(), parameters.ToArray());
            if (lsShop != null && lsShop.Count > 0)
            {
                return lsShop.ToList();
            }
            return null;
        }
        /// <summary>
        /// 获取店铺信息并且得到 距离
        /// </summary>
        public DataSet GetModelShopId(int id, string longitude, string latitude)
        {
            var strSql = new StringBuilder();
            strSql.Append("select Id, Name, Description, AreaId, Address, BusinessStatus, CloseTimeStart, CloseTimeEnd, WechatParamQrCodeId, Created, Updated, CreatedBy, UpdatedBy, Longitude, Latitude, LegalPerson, LegalPersonMobile, PostNumber, BusinessLicenseNo, Logo, SalesVolume, IsOpenOrderGrab, Enabled, SalesTerritory, MarketAreaName, BelongDistributor, Tel, IndustryId, SeparatedIndustryId, CanSendCard, FixedQrCode, IsHeadquarters, ParentShopId, IsActive, CanFrontView, IsDelete,dbo.fnGetDistance(@Latitude, @Longitude, Latitude,  Longitude) AS Distance ");
            strSql.Append("  from Shops ");
            strSql.Append(" where Id=@Id  and IsDelete=0");
            SqlParameter[] parameters = {
			    new SqlParameter("@Id", SqlDbType.Int,4),
                new SqlParameter("@Longitude", longitude),
	            new SqlParameter("@Latitude", latitude),
			};
            parameters[0].Value = id;
            var ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }
        /// <summary>
        /// 获取用户访问过/购买过的店铺列表
        /// </summary>
        /// <param name="consumerId"></param>
        /// <returns></returns>
        public List<Shops> GetShopListOfConsumerVisited(int consumerId)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("SELECT sh.Id, sh.Name, sh.Description, sh.AreaId, sh.Address,sh. BusinessStatus, sh.CloseTimeStart, sh.CloseTimeEnd, ");
            sbSql.Append("sh.WechatParamQrCodeId, sh.Created, sh.Updated, sh.CreatedBy, sh.UpdatedBy, sh.Longitude, sh.Latitude, ");
            sbSql.Append("sh.LegalPerson, sh.LegalPersonMobile, sh.PostNumber, sh.BusinessLicenseNo, sh.Logo, sh.SalesVolume, ");
            sbSql.Append("sh.IsOpenOrderGrab, sh.Enabled, sh.SalesTerritory, sh.MarketAreaName, sh.BelongDistributor, sh.Tel, sh.IndustryId, sh.SeparatedIndustryId, sh.CanSendCard, sh.FixedQrCode, sh.IsHeadquarters, sh.ParentShopId, sh.IsActive, sh.CanFrontView, sh.IsDelete, cs.Id AS CsId  ");
            sbSql.Append("FROM Shops sh ");
            sbSql.Append("INNER JOIN ConsumerShop cs ON cs.ShopId=sh.Id ");
            sbSql.Append("INNER JOIN Consumer co ON co.Id=cs.ConsumerId ");
            sbSql.Append("WHERE sh.IsDelete=0 AND cs.IsDelete=0 AND cs.ConsumerId=@ConsumerId AND co.IsDelete=0 ");
            sbSql.Append("ORDER BY cs.Id DESC ");
            SqlParameter[] parameters = {
					new SqlParameter("@ConsumerId", SqlDbType.Int,4)
			};
            parameters[0].Value = consumerId;
            var lsShop = DbHelperSQL.ExecuteSqlList<Shops>(sbSql.ToString(), parameters);
            if (lsShop != null && lsShop.Count > 0)
            {
                return lsShop.DistinctBy(p => p.Id).ToList();
            }
            return null;
        }
        /// <summary>
        /// 卡劵适用店铺
        /// </summary>
        /// <param name="cloudCardId"></param>
        /// <returns></returns>
        public List<Shops> GetApplyShopList(int cloudCardId)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("select  s.* from Shops s ");
            sbSql.Append("left join CardRuleBase crb on crb.ShopId=s.Id ");
            sbSql.Append("left join CardRuleDetail crd on crd.RuleBaseId=crb.Id where  crd.CloudCardId=@CloudCardId ");
            sbSql.Append("and crb.Id IN(select max(id) from  CardRuleBase where shopid=s.ID and isdelete=0  and StartTime<=CONVERT(varchar(100), getdate(), 23) and EndTime>=CONVERT(varchar (100), getdate(), 23) )");

            SqlParameter[] parameters = {
					new SqlParameter("@CloudCardId", SqlDbType.Int,4)
			};
            parameters[0].Value = cloudCardId;
            var lsShop = DbHelperSQL.ExecuteSqlList<Shops>(sbSql.ToString(), parameters);
            if (lsShop != null && lsShop.Count > 0)
            {
                return lsShop.DistinctBy(p => p.Id).ToList();
            }
            return null;
        }

        /// <summary>
        /// 获取店铺线上支付已完成订单收入总金额
        /// </summary>
        /// <param name="shopId"></param>
        /// <returns></returns>
        public double GetTotalIncomeOfShopId(int shopId)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("SELECT SUM(os.DiscountPrice) AS TotalIncome ");
            sbSql.Append("From Orders os ");
            sbSql.Append("INNER JOIN Shops ss ON os.ShopId=ss.Id ");
            sbSql.Append("INNER JOIN PaymentRecords pr ON pr.OrderId=os.Id ");
            sbSql.Append("WHERE ss.IsDelete=0 AND os.IsDelete=0 AND os.IsPayCompleted=1 AND os.OrderStatus=@OrderStatus ");
            sbSql.Append("AND os.ShopId=@ShopId AND pr.PayResult=1  and pr.IsDelete=0 AND os.CanWithdraw=1");

            SqlParameter[] parameters = {
					new SqlParameter("@ShopId", SqlDbType.Int,4),
                    new SqlParameter("@OrderStatus", SqlDbType.Int,4)
			};
            parameters[0].Value = shopId;
            parameters[1].Value = (int)CommonEnum.OrderStatus.Finished;

            var obj = DbHelperSQL.GetSingle(sbSql.ToString(), parameters);
            return (double)(obj == null ? 0 : Convert.ToDecimal(obj.ToString()));
        }
        /// <summary>
        /// 获取当前店铺的提成佣金
        /// </summary>
        /// <param name="shopId"></param>
        /// <returns></returns>
        public double GetTotalShopCommission(int shopId)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("SELECT  isnull(sum(od.shopCommission),0) as TotalShopCommission ");
            sbSql.Append("FROM [OrderDetail] od left join Orders o on o.id=od.OrderId  ");
            sbSql.Append(" where od.IsDelete=0 and o.isDelete=0 ");
            sbSql.Append(" and o.ShopId=@ShopId  and o.OrderStatus=@OrderStatus ");
            SqlParameter[] parameters = {
					new SqlParameter("@ShopId", SqlDbType.Int,4),
                    new SqlParameter("@OrderStatus", SqlDbType.Int,4)
			};
            parameters[0].Value = shopId;
            parameters[1].Value = (int)CommonEnum.OrderStatus.Finished;
            var obj = DbHelperSQL.GetSingle(sbSql.ToString(), parameters);
            return (double)(obj == null ? 0 : Convert.ToDecimal(obj.ToString()));
        }
        /// <summary>
        /// 获取店铺优惠券使用订单金额为0已完成订单收入总金额
        /// </summary>
        /// <param name="shopId"></param>
        /// <returns></returns>
        public double GetCashPayCouponOrderZeroPayTotalIncomeOfShopId(int shopId)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("SELECT SUM(os.DiscountPrice) AS TotalIncome ");
            sbSql.Append("From Orders os ");
            sbSql.Append("INNER JOIN Shops ss ON os.ShopId=ss.Id ");
            sbSql.Append("WHERE ss.IsDelete=0 AND os.IsDelete=0 AND os.OrderStatus=@OrderStatus ");
            sbSql.Append("AND os.ShopId=@ShopId AND os.PayWay=@PayWay AND os.PayPrice=0 ");
            sbSql.Append("AND os.Id IN (SELECT oc.OrderId FROM OrderCoupon oc WHERE oc.OrderId=os.Id AND oc.EnableCouponWithDraw=1 AND oc.IsDelete=0) ");
            //sbSql.Append("AND os.ConsumerOrderType=@ConsumerOrderType ");

            SqlParameter[] parameters = {
					new SqlParameter("@ShopId", SqlDbType.Int,4),
                    new SqlParameter("@OrderStatus", SqlDbType.Int,4),
                    new SqlParameter("@PayWay", SqlDbType.Int,4),
                    //new SqlParameter("@ConsumerOrderType", SqlDbType.Int, 4)
			};
            parameters[0].Value = shopId;
            parameters[1].Value = (int)CommonEnum.OrderStatus.Finished;
            parameters[2].Value = (int)CommonEnum.OrderPayWay.Cash;
            //parameters[3].Value = (int) CommonEnum.ConsumerOrderType.GfcConsumerOrder;

            var obj = DbHelperSQL.GetSingle(sbSql.ToString(), parameters);
            return (double)(obj == null ? 0 : Convert.ToDecimal(obj.ToString()));
        }

        /// <summary>
        /// 获取店铺优惠券使用订单金额不为为0已完成订单中使用优惠券的商品收入总金额
        /// </summary>
        /// <param name="shopId"></param>
        /// <returns></returns>
        public double GetUnOnlinePayCouponOrderUnZeroPayTotalIncomeOfShopId(int shopId)
        {
            var sbSql = new StringBuilder();
            sbSql.Append("SELECT ISNULL(SUM(TB1.Price*TB1.CouponAmount), 0) AS TotalIncome ");
            sbSql.Append("FROM  ");
            sbSql.Append("( ");
            sbSql.Append("SELECT COUNT(od.id) AS CouponAmount, od.Id, od.Price ");
            sbSql.Append("From OrderDetail od  ");
            sbSql.Append("INNER JOIN Orders os ON os.Id=od.OrderId  ");
            sbSql.Append("INNER JOIN Shops ss ON os.ShopId=ss.Id  ");
            sbSql.Append("INNER JOIN ShopCommodity sc ON sc.Id=od.ShopCommodityId  ");
            sbSql.Append("INNER JOIN CouponCommodityLib ccl ON ccl.CommodityLibId=sc.CommodityLibId  ");
            sbSql.Append("INNER JOIN OrderCoupon cc ON cc.OrderId=os.Id ");
            sbSql.Append("WHERE ss.IsDelete=0 AND os.IsDelete=0 AND os.OrderStatus=@OrderStatus AND od.IsGift=0  ");
            sbSql.Append("AND os.ShopId=@ShopId AND os.PayWay<>@PayWay AND os.PayPrice>0 and ccl.IsDelete=0 ");
            sbSql.Append("AND cc.EnableCouponWithDraw=1 AND cc.IsDelete=0 ");
            //sbSql.Append("AND os.ConsumerOrderType=@ConsumerOrderType AND cc.EnableCouponWithDraw=1 ");
            sbSql.Append("GROUP BY od.id, od.Price ");
            sbSql.Append(") TB1 ");

            SqlParameter[] parameters = {
					new SqlParameter("@ShopId", SqlDbType.Int,4),
                    new SqlParameter("@OrderStatus", SqlDbType.Int,4),
                    new SqlParameter("@PayWay", SqlDbType.Int,4),
                    //new SqlParameter("@ConsumerOrderType", SqlDbType.Int, 4)
			};
            parameters[0].Value = shopId;
            parameters[1].Value = (int)CommonEnum.OrderStatus.Finished;
            parameters[2].Value = (int)CommonEnum.OrderPayWay.WeChat;
            //parameters[3].Value = (int)CommonEnum.ConsumerOrderType.GfcConsumerOrder;

            var obj = DbHelperSQL.GetSingle(sbSql.ToString(), parameters);
            return (double)(obj == null ? 0 : Convert.ToDecimal(obj.ToString()));
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Shops GetShopInfo(int id)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * from Shops ");
            strSql.Append(" where Id=@Id  and IsDelete=0");
            SqlParameter[] parameters = {
				new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = id;
            return DbHelperSQL.ExecuteReader<Shops>(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 根据条件获取店铺总数
        /// </summary>
        /// <remarks>2016-08-22 万乐园 创建</remarks>
        public int GetTotalCount(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("select count(1) FROM Shops where IsDelete=0");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" and " + strWhere);
            }
            return int.Parse(DbHelperSQL.GetSingle(strSql.ToString()).ToString());
        }

        /// <summary>
        /// 根据店铺名称关键字获得数据列表
        /// </summary>
        /// <remarks>2016-08-23 万乐园 创建</remarks>
        public DataSet GetListByName(string keyWords, bool? isActive, string inIds = null)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * FROM Shops where IsDelete=0");
            var parameters = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(keyWords))
            {
                strSql.Append(" and [Name] like @keyWord");
                parameters.Add(new SqlParameter { ParameterName = "@keyWord", Value = "%" + keyWords + "%" });
            }
            if (isActive.HasValue)
            {
                strSql.Append(" and IsActive=" + (isActive == true ? 1 : 0));
            }
            if (!string.IsNullOrEmpty(inIds))
            {
                strSql.Append(" and Id in (" + inIds.Trim(',') + ")");
            }
            return DbHelperSQL.Query(strSql.ToString(), parameters.ToArray());
        }

        //导出方法
        public DataSet GetLists(string strWhere)
        {
            var strSql = new StringBuilder();
            strSql.Append("select Id as '编号',Name as '店铺名',Address as '店铺地址',LegalPerson as '法人',LegalPersonMobile as '法人联系电话',SalesTerritory as '片区', MarketAreaName as '市场' ,Tel as '电话' ");
            strSql.Append(" FROM Shops where IsDelete=0 ");
            if (!string.IsNullOrEmpty(strWhere))
            {
                strSql.Append(" and " + strWhere);
            }
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 根据店铺全称得到一个对象实体
        /// </summary>
        /// <remarks>2016-08-31 万乐园 创建</remarks>
        public DataSet GetModelByName(string name)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * from Shops ");
            strSql.Append(" where Name=@Name and IsDelete=0");
            SqlParameter[] parameters = {
				new SqlParameter("@Name", SqlDbType.NVarChar,50)
			};
            parameters[0].Value = name;
            var ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }

        /// <summary>
        /// 根据店铺电话得到一个对象实体
        /// </summary>
        /// <remarks>2016-09-01 万乐园 创建</remarks>
        public DataSet GetModelByTel(string tel)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * from Shops ");
            strSql.Append(" where Tel=@Tel and IsDelete=0");
            SqlParameter[] parameters = {
				new SqlParameter("@Tel", SqlDbType.NVarChar,50)
			};
            parameters[0].Value = tel;
            var ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Shops GetModelById(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * from Shops ");
            strSql.Append(" where Id=@Id  and IsDelete=0");
            SqlParameter[] parameters = {
				new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;
            return DbHelperSQL.ExecuteReader<Shops>(strSql.ToString(), parameters);
        }
        public Shops GetModelByIdWhereIndustryId(int Id)
        {
            var strSql = new StringBuilder();
            strSql.Append("select  ids.ParentId,ids.Name as IndustryName,Shops.* from Shops left join Industry ids on ids.Id=Shops.IndustryId ");
            strSql.Append(" where Shops.Id=@Id  and Shops.IsDelete=0");
            SqlParameter[] parameters = {
				new SqlParameter("@Id", SqlDbType.Int,4)
			};
            parameters[0].Value = Id;
            return DbHelperSQL.ExecuteReader<Shops>(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 根据店铺名称关键字获得分店数据列表
        /// </summary>
        public DataSet GetBranchShopList(string keyWords, int parentShopId)
        {
            var strSql = new StringBuilder();
            strSql.Append("select * FROM Shops where IsDelete=0");
            var parameters = new List<SqlParameter>();
            if (!string.IsNullOrEmpty(keyWords))
            {
                strSql.Append(" and [Name] like @keyWord");
                parameters.Add(new SqlParameter { ParameterName = "@keyWord", Value = "%" + keyWords + "%" });
            }
            if (parentShopId > 0)
            {
                strSql.Append(" and ParentShopId=" + parentShopId);
            }
            return DbHelperSQL.Query(strSql.ToString(), parameters.ToArray());
        }
        #endregion
    }
}
